/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package Br.Com.Cds.Academia.Aluno;

import Br.Com.Cds.Academia.Banco.BancoBean;
import Br.Com.Cds.Academia.DAO.AcessoMySQL;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author Pedro
 */
public class AlunoControl {
    
    PreparedStatement pstm;
    ResultSet rs;
    
    
    String consultaaluno = "SELECT * FROM ALUNO WHERE NOME LIKE ?";
    String cadastraaluno = "INSERT INTO ALUNO (STATUS,VISITANTE,DIASATRASO,NOME,RG,ORGAOEMISSOR,ESTADOEMISSOR,CPF,CEP,ENDERENCO,CIDADE,BAIRRO,NUMERO,COMPLEMENTO,UF,TELEFONE,CELULAR,EMAIL,AUTORIZACAO,DATANASCIMENTO,IDADE,CAMINHOFOTO,BIOMETRIA) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    String alteraraaluno = "UPDATE ALUNO SET STATUS=?,VISITANTE=?,DIASATRASO=?,NOME=?,RG=?,ORGAOEMISSOR=?,ESTADOEMISSOR=?,CPF=?,CEP=?,ENDERECO=?,CIDADE=?,BAIRRO=?,NUMERO=?,COMPLEMENTO=?,UF=?,TELEFONE=?,CELULAR=?,EMAIL=?,AUTORIZACAO=?,DATANASCIMENTO=?,IDADE=?,CAMINHOFOTO=?,BIOMETRIA=? WHERE IDALUNO=?";
    String excluiraluno = "DELETE FROM ALUNO WHERE IDALUNO=?";
    
    
    
     public void cadastraAluno (AlunoBean aluno){
        try {
            AcessoMySQL mysql = new AcessoMySQL();
            pstm = (PreparedStatement) mysql.conectar().prepareStatement(cadastraaluno);
            pstm.setString(1,aluno.getStatus());
            pstm.setString(2, aluno.getVisitante());
            pstm.setDate(3, aluno.getDiasatraso());
            pstm.setString(4, aluno.getNome());
            pstm.setString(5, aluno.getRg());
            pstm.setString(6, aluno.getOrgaoemissor());
            pstm.setString(7, aluno.getEstadoemissor());
            pstm.setString(8, aluno.getCpf());
            pstm.setString(9, aluno.getCep());
            pstm.setString(10, aluno.getEndereco());
            pstm.setString(11, aluno.getCidade());
            pstm.setString(12, aluno.getBairro());
            pstm.setString(13, aluno.getNumero());
            pstm.setString(14, aluno.getComplemento());
            pstm.setString(15, aluno.getUf());
            pstm.setString(16, aluno.getTelefone());
            pstm.setString(17, aluno.getCelular());
            pstm.setString(18, aluno.getEmail());
            pstm.setString(19, aluno.getAutorizacao());
            pstm.setDate(20, aluno.getDatanascimento());
            pstm.setString(21, aluno.getIdade());
            pstm.setString(22, aluno.getCaminhofoto());
            pstm.setString(23, aluno.getBiometria());
            pstm.executeUpdate();
            mysql.desconectar();
    
        } catch (SQLException e) {
            e.printStackTrace ();
            JOptionPane.showMessageDialog(null, "SQL EXCEPTION ! ERRO AO TENTAR CADASTRAR UM ALUNO!","CDS ACADEMIA",JOptionPane.ERROR_MESSAGE); 
        }
}


public void alterarAluno (AlunoBean aluno){
    
    try {
        AcessoMySQL mysql = new AcessoMySQL();
        pstm = (PreparedStatement) mysql.conectar().prepareStatement(alteraraaluno);
            pstm.setString(1,aluno.getStatus());
            pstm.setString(2, aluno.getVisitante());
            pstm.setDate(3, aluno.getDiasatraso());
            pstm.setString(4, aluno.getNome());
            pstm.setString(5, aluno.getRg());
            pstm.setString(6, aluno.getOrgaoemissor());
            pstm.setString(7, aluno.getEstadoemissor());
            pstm.setString(8, aluno.getCpf());
            pstm.setString(9, aluno.getCep());
            pstm.setString(10, aluno.getEndereco());
            pstm.setString(11, aluno.getCidade());
            pstm.setString(12, aluno.getBairro());
            pstm.setString(13, aluno.getNumero());
            pstm.setString(14, aluno.getComplemento());
            pstm.setString(15, aluno.getUf());
            pstm.setString(16, aluno.getTelefone());
            pstm.setString(17, aluno.getCelular());
            pstm.setString(18, aluno.getEmail());
            pstm.setString(19, aluno.getAutorizacao());
            pstm.setDate(20, aluno.getDatanascimento());
            pstm.setString(21, aluno.getIdade());
            pstm.setString(22, aluno.getCaminhofoto());
            pstm.setString(23, aluno.getBiometria());
            pstm.setInt(24, aluno.getIdaluno());
            pstm.executeUpdate();
            mysql.desconectar(); 
    } catch (Exception e) {
        e.printStackTrace();
        JOptionPane.showMessageDialog(null,"SQL EXCEPTION ! ERRO AO TENTAR ALTERAR UM ALUNO!","CDS ACADEMIA",JOptionPane.ERROR_MESSAGE);
    }
}

    
  public List <AlunoBean> listarAluno (String nomealuno){
         List <AlunoBean> alunos = new ArrayList ();
         
         try {
             AcessoMySQL mysql = new AcessoMySQL();
             pstm = (PreparedStatement) mysql.conectar().prepareStatement(consultaaluno);
             pstm.setString(1, nomealuno);
             rs = pstm.executeQuery();
             AlunoBean alu;
             
             while (rs.next()){
                alu = new AlunoBean ();
                alu.setIdaluno(rs.getInt("IDALUNO"));          
                alu.setStatus(rs.getString("STATUS"));  
                alu.setVisitante(rs.getString("VISITANTE")); 
                alu.setNome(rs.getString("NOME")); 
                alu.setRg(rs.getString("RG"));  
                alu.setOrgaoemissor(rs.getString("ORGAOEMISSOR"));   
                alu.setEstadoemissor(rs.getString("ESTADOEMISSOR"));   
                alu.setCpf(rs.getString("CPF"));   
                alu.setCep(rs.getString("CEP"));   
                alu.setEndereco(rs.getString("ENDERECO"));   
                alu.setCidade(rs.getString("CIDADE"));   
                alu.setBairro(rs.getString("BAIRRO"));   
                alu.setNumero(rs.getString("NUMERO"));   
                alu.setComplemento(rs.getString("COMPLEMENTO"));   
                alu.setUf(rs.getString("UF"));   
                alu.setTelefone(rs.getString("TELEFONE"));   
                alu.setCelular(rs.getString("CELULAR"));   
                alu.setEmail(rs.getString("EMAIL"));   
                alu.setAutorizacao(rs.getString("AUTORIZACAO"));   
                alu.setDatanascimento(rs.getDate("DATANASCIMENTO"));   
                alu.setIdade(rs.getString("IDADE"));   
                alu.setCaminhofoto(rs.getString("CAMINHOFOTO"));   
                alu.setBiometria(rs.getString("BIOMETRIA"));   
                alunos.add(alu);  
             }    
      } catch (Exception e) {
        e.printStackTrace();
         JOptionPane.showMessageDialog(null,"SQL EXCEPTION - ERRO AO TENTAR REALIZAR UM ALUNO","CDS ACADEMIA",JOptionPane.ERROR_MESSAGE);
        
      }
        return alunos;
      
  }
    
    
  public void excluirAluno (int idaluno){
      
      try {
          AcessoMySQL mysql = new AcessoMySQL();
          pstm = (PreparedStatement) mysql.conectar().prepareStatement(excluiraluno);
          pstm.setInt(1,idaluno);
          pstm.executeUpdate();
          mysql.desconectar();
            
      } catch (Exception e) {
          e.printStackTrace();
          JOptionPane.showMessageDialog(null,"SQL EXCEPTION - ERRO AO TENTAR EXCLUIR O ALUNO","CDS ACADEMIA",JOptionPane.ERROR_MESSAGE);
      }
      
      
      
      
  }
    
    
    
    
    
}
